﻿//-----------------------------------------------------------------------
// <copyright file="CommandTest.cs" company="NextLine Software">
//     Copyright (c) NextLine Software. All rights reserved.
// </copyright>
//-----------------------------------------------------------------------

namespace NextLine.Data.SQLite.Tests
{
    using System;
    using System.Data;
    using Microsoft.VisualStudio.TestTools.UnitTesting;

    [TestClass]
    public class CommandTest
    {
        [TestMethod]
        public void CreateCommandTest()
        {
            var connection = new SQLiteConnection();
            var command = connection.CreateCommand();
            Assert.IsNotNull(command);
            Assert.IsInstanceOfType(command, typeof(SQLiteCommand));
            Assert.AreEqual(connection, command.Connection);
        }

        [TestMethod]
        public void TimeoutTest()
        {
            var command = new SQLiteCommand();
            Assert.AreEqual(30, command.CommandTimeout);
            command.CommandTimeout = 40;
            Assert.AreEqual(40, command.CommandTimeout);
        }

        [TestMethod]
        [ExpectedException(typeof(ArgumentException))]
        public void InvalidTimeoutTest()
        {
            var command = new SQLiteCommand();
            command.CommandTimeout = -1;
        }

        [TestMethod]
        public void CommandTypeTest()
        {
            var command = new SQLiteCommand();
            Assert.AreEqual(CommandType.Text, command.CommandType);
            command.CommandType = CommandType.Text;
        }

        [TestMethod]
        [ExpectedException(typeof(NotSupportedException))]
        public void InvalidCommandTypeTest()
        {
            var command = new SQLiteCommand();
            command.CommandType = CommandType.StoredProcedure;
        }

        [TestMethod]
        public void CreateTableTest()
        {
            string databaseName = Guid.NewGuid().ToString();
            using (var connection = new SQLiteConnection(databaseName))
            {
                connection.Open();
                Helpers.CreateTestTable(connection);
                var command = connection.CreateCommand();
                command.CommandText = "SELECT COUNT(1) FROM sqlite_master WHERE type='table' AND name='TestTable'";
                Assert.AreEqual(1, (long)command.ExecuteScalar());
            }
        }

        [TestMethod]
        public void TableExistsTest()
        {
            string databaseName = Guid.NewGuid().ToString();
            using (var connection = new SQLiteConnection(databaseName))
            {
                connection.Open();
                Helpers.CreateTestTable(connection);
                var command = connection.CreateCommand();
                command.CommandText = "SELECT COUNT(1) FROM sqlite_master WHERE type='table' AND name='TestTable'";
                Assert.AreEqual(1, (long)command.ExecuteScalar());

                command.CommandText = "SELECT COUNT(1) FROM sqlite_master WHERE type='table' AND name='NonExistingTable'";
                Assert.AreEqual(0, (long)command.ExecuteScalar());
            }
        }
        
        [TestMethod]
        public void PrepareTest()
        {
            string databaseName = Guid.NewGuid().ToString();
            using (var connection = new SQLiteConnection(databaseName))
            {
                connection.Open();
                Helpers.CreateTestTable(connection);
                var command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM TestTable";
                command.Prepare();

                // can all prepare twice
                command.Prepare();
            }
        }

        [TestMethod]
        [ExpectedException(typeof(InvalidOperationException))]
        public void PrepareFailureNoConnectionTest()
        {
            var command = new SQLiteCommand();
            command.Prepare();
        }

        [TestMethod]
        [ExpectedException(typeof(InvalidOperationException))]
        public void PrepareFailureClosedConnectionTest()
        {
            string databaseName = Guid.NewGuid().ToString();
            using (var connection = new SQLiteConnection(databaseName))
            {
                var command = connection.CreateCommand();
                command.Prepare();
            }
        }

        [TestMethod]
        [ExpectedException(typeof(SQLiteException))]
        public void PrepareFailureNonExistingTableTest()
        {
            string databaseName = Guid.NewGuid().ToString();
            using (var connection = new SQLiteConnection(databaseName))
            {
                connection.Open();
                Helpers.CreateTestTable(connection);
                var command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM TestTable2";
                command.Prepare();
            }
        }

        [TestMethod]
        public void InsertRowTest()
        {
            string databaseName = Guid.NewGuid().ToString();
            using (var connection = new SQLiteConnection(databaseName))
            {
                connection.Open();
                Helpers.CreateTestTable(connection);
                var command = connection.CreateCommand();
                command.CommandText = "SELECT Count(1) FROM TestTable";
                long currentCount = (long)command.ExecuteScalar();
                Assert.AreEqual(0, currentCount);

                // insert a row
                command.CommandText = "INSERT INTO TestTable (ID, StringValue, IntValue, DoubleValue) VALUES (1, 'test', 5, 1.5)";
                command.ExecuteNonQuery();

                command.CommandText = "SELECT Count(1) FROM TestTable";
                currentCount = (long)command.ExecuteScalar();
                Assert.AreEqual(1, currentCount);

                // validate the inserted row
                command.CommandText = "SELECT * FROM TestTable";
                using (IDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        Assert.AreEqual(1, (long)reader["ID"]);
                        Assert.AreEqual("test", (string)reader["StringValue"]);
                        Assert.AreEqual(5, (long)reader["IntValue"]);
                        Assert.AreEqual(1.5, (double)reader["DoubleValue"]);
                    }
                    else
                    {
                        Assert.Fail("Could not read row value");
                    }

                    Assert.IsFalse(reader.Read());
                }
            }
        }

        [TestMethod]
        public void ParameterizedQueryTest()
        {
            string databaseName = Guid.NewGuid().ToString();
            using (var connection = new SQLiteConnection(databaseName))
            {
                connection.Open();
                Helpers.CreateTestTable(connection);
                var command = connection.CreateCommand();

                // insert a couple rows
                command.CommandText = "INSERT INTO TestTable (ID, StringValue, IntValue, DoubleValue) VALUES (@ID, @StringValue, @IntValue, @DoubleValue)";
                command.Parameters.Add(new SQLiteDataParameter("@ID", 1));
                command.Parameters.Add(new SQLiteDataParameter("@StringValue", "elia"));
                command.Parameters.Add(new SQLiteDataParameter("@IntValue", 7));
                command.Parameters.Add(new SQLiteDataParameter("@DoubleValue", 11.23));
                command.ExecuteNonQuery();

                command.Parameters["@ID"] = 2;
                command.Parameters["@StringValue"] = "karagiannis";
                command.Parameters["@IntValue"] = 1982;
                command.Parameters["@DoubleValue"] = 12.2;
                command.ExecuteNonQuery();

                // insert using wildcards
                command.CommandText = "INSERT INTO TestTable (ID, StringValue, IntValue, DoubleValue) VALUES (?, ?, ?, ?)";
                command.Parameters.Clear();
                command.Parameters.Add(new SQLiteDataParameter(3));
                command.Parameters.Add(new SQLiteDataParameter("elia karagiannis"));
                command.Parameters.Add(new SQLiteDataParameter(1234));
                command.Parameters.Add(new SQLiteDataParameter(12.34));
                command.ExecuteNonQuery();

                command.CommandText = "SELECT Count(1) FROM TestTable";
                Assert.AreEqual(3, (long)command.ExecuteScalar());

                // validate the inserted row
                command.CommandText = "SELECT * FROM TestTable ORDER BY ID ASC";
                using (IDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        Assert.AreEqual(1, (long)reader["ID"]);
                        Assert.AreEqual("elia", (string)reader["StringValue"]);
                        Assert.AreEqual(7, (long)reader["IntValue"]);
                        Assert.AreEqual(11.23, (double)reader["DoubleValue"]);
                    }
                    else
                    {
                        Assert.Fail("Could not read first row value");
                    }

                    if (reader.Read())
                    {
                        Assert.AreEqual(2, (long)reader["ID"]);
                        Assert.AreEqual("karagiannis", (string)reader["StringValue"]);
                        Assert.AreEqual(1982, (long)reader["IntValue"]);
                        Assert.AreEqual(12.2, (double)reader["DoubleValue"]);
                    }
                    else
                    {
                        Assert.Fail("Could not read second row value");
                    }

                    if (reader.Read())
                    {
                        Assert.AreEqual(3, (long)reader["ID"]);
                        Assert.AreEqual("elia karagiannis", (string)reader["StringValue"]);
                        Assert.AreEqual(1234, (long)reader["IntValue"]);
                        Assert.AreEqual(12.34, (double)reader["DoubleValue"]);
                    }
                    else
                    {
                        Assert.Fail("Could not read third row value");
                    }

                    Assert.IsFalse(reader.Read());
                }

                // validate the inserted row sorted
                command.CommandText = "SELECT * FROM TestTable ORDER BY ID DESC";
                using (IDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        Assert.AreEqual(3, (long)reader["ID"]);
                    }
                    else
                    {
                        Assert.Fail("Could not read first row value");
                    }

                    if (reader.Read())
                    {
                        Assert.AreEqual(2, (long)reader["ID"]);
                    }
                    else
                    {
                        Assert.Fail("Could not read second row value");
                    }

                    if (reader.Read())
                    {
                        Assert.AreEqual(1, (long)reader["ID"]);
                    }
                    else
                    {
                        Assert.Fail("Could not read third row value");
                    }

                    Assert.IsFalse(reader.Read());
                }
            }
        }
    }
}
